﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
using Apps.Models;
using System.Data;
using Apps.Common;

namespace Apps.DAL.HC
{
    public partial class HC_FORM_Repository : DbContext<HC_ITEMS>
    {
        HC_ITEMS_Repository hC_ITEMS_Repository = new HC_ITEMS_Repository();
        public DataTable GetList(string itemId, string beginTime, string endTime, int formState, int pageIndex, int pageSize, string sortFiled, string orderBy)
        {
            try
            {
                var items = new HC_ITEMS_Repository().GetFieldInfoByItemId(itemId);
                if (items == null) return null;
                string fields = string.Empty;
                int maxPage = pageIndex * pageSize;
                int minPage = (pageIndex - 1) * pageSize + 1;
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM ( ");
                sb.AppendFormat("SELECT t.*, ROW_NUMBER() OVER (ORDER BY {0} {1}) RN ", sortFiled, orderBy);
                /*sb.Append("FROM (SELECT ID,ADDTIME,ADDUSER,UPDATETIME,UPDATEUSER,AUDITTIME,AUDITSER,STATE, ");*/
                sb.AppendFormat("FROM HC_{0} t WHERE 1=1", items.itemVo.ITEM_NAME);
                if (!string.IsNullOrEmpty(beginTime))
                {
                    sb.AppendFormat(" AND t.ADDTIME >= to_date('{0} 00:00:00', 'yyyy-mm-dd hh24:mi:ss')", beginTime);
                }
                if (!string.IsNullOrEmpty(endTime))
                {
                    sb.AppendFormat(" AND t.ADDTIME <= to_date('{0} 23:59:59', 'yyyy-mm-dd hh24:mi:ss')", endTime);
                }
                if (formState != -2)
                {
                    sb.AppendFormat(" AND t.STATE={0}", formState);
                }
                sb.AppendFormat(") WHERE RN BETWEEN {0} AND {1}", minPage, maxPage);
                SugarParameter[] para = { };
                return Db.Ado.GetDataTable(sb.ToString());
            }
            catch
            {
                return new DataTable();
            }
        }

        public DataTable GetList(string itemId, string beginTime, string endTime, int formState)
        {
            var items = new HC_ITEMS_Repository().GetById(itemId);
            if (items == null) return null;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT * FROM HC_{0} t where 1=1", items.ITEM_NAME);
            if (!string.IsNullOrEmpty(beginTime))
            {
                sb.AppendFormat(" AND t.ADDTIME >= to_date('{0} 00:00:00', 'yyyy-mm-dd hh24:mi:ss')", beginTime);
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                sb.AppendFormat(" AND t.ADDTIME <= to_date('{0} 23:59:59', 'yyyy-mm-dd hh24:mi:ss')", endTime);
            }
            if (formState != -2)
            {
                sb.AppendFormat(" AND t.STATE={0}", formState);
            }
            SugarParameter[] para = { };
            return Db.Ado.GetDataTable(sb.ToString(), para);
        }

        public int GetCount(string itemId, string beginTime, string endTime, int formState)
        {
            var items = new HC_ITEMS_Repository().GetById(itemId);
            if (items == null) return 0;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM HC_{0} t WHERE 1=1", items.ITEM_NAME);
            if (!string.IsNullOrEmpty(beginTime))
            {
                sb.AppendFormat(" AND t.ADDTIME >= to_date('{0} 00:00:00', 'yyyy-mm-dd hh24:mi:ss')", beginTime);
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                sb.AppendFormat(" AND t.ADDTIME <= to_date('{0} 23:59:59', 'yyyy-mm-dd hh24:mi:ss')", endTime);
            }
            if (formState != -2)
            {
                sb.AppendFormat(" AND t.STATE={0}", formState);
            }
            return Db.Ado.GetInt(sb.ToString());
        }
        public bool DoAdd(string itemId, Dictionary<string, object> dic)
        {
            try
            {
                Db.Ado.BeginTran();
                List<V_HC_ITEM_FIELD_VALUE> list = hC_ITEMS_Repository.Get_FIELD_REFERENCE_VALUE_List(itemId);
                var items = new HC_ITEMS_Repository().GetById(itemId);
                if (items == null) return false;
                StringBuilder colSb = new StringBuilder();
                colSb.AppendFormat("INSERT INTO HC_{0} (", items.ITEM_NAME);
                //sb.AppendFormat("ID,ADDTIME,ADDUSER,UPDATETIME,UPDATEUSER,AUDITTIME,AUDITSER,STATE,");
                foreach (KeyValuePair<string, object> item in dic)
                {
                    colSb.AppendFormat("{0},", item.Key.ToUpper());
                }
                string colSql = colSb.ToString().TrimEnd(',');
                StringBuilder parSb = new StringBuilder();
                parSb.Append(") ");
                parSb.Append("VALUES ");
                parSb.Append("(");
                foreach (KeyValuePair<string, object> item in dic)
                {
                    parSb.AppendFormat(":{0},", item.Key.ToUpper());
                }
                string parSql = parSb.ToString().TrimEnd(',');
                string sql = colSql + parSql + ")";
                sql += " returning ID into :ID";
                var paraList = new List<SugarParameter>();
                foreach (KeyValuePair<string, object> item in dic)
                {
                    paraList.Add(new SugarParameter(string.Format(":{0}", item.Key.ToUpper()), item.Value));
                }
                string formId = string.Empty;
                if (Db.Ado.ExecuteCommand(sql.ToString(), paraList) > 0)
                {
                    formId = Db.Ado.GetString(string.Format("SELECT ID FROM(Select ID From HC_{0} ORDER BY ADDTIME DESC) WHERE ROWNUM = 1", items.ITEM_NAME), new List<SugarParameter>());
                }
                List<HC_FORM_REFERENCE_VALUE> formValueList = new List<HC_FORM_REFERENCE_VALUE>();
                foreach (var item in list)
                {
                    formValueList.Add(new HC_FORM_REFERENCE_VALUE()
                    {
                        ID = ResultHelper.NewId,
                        FORMID = formId,
                        ITEMID = item.ITEM_ID,
                        FIELDID = item.ID,
                        BASEVALUE = item.BASEVALUE,
                        MINVALUE = item.MINVALUE,
                        MAXVALUE = item.MAXVALUE,
                    });
                }
                Db.Insertable(formValueList).ExecuteCommand();
                Db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                Db.Ado.RollbackTran();
                return false;
            }

        }

        public bool DoEdit(string itemId, Dictionary<string, object> dic)
        {
            var items = new HC_ITEMS_Repository().GetById(itemId);
            if (items == null) return false;
            StringBuilder colSb = new StringBuilder();
            colSb.AppendFormat("UPDATE HC_{0} TA SET ", items.ITEM_NAME);
            foreach (KeyValuePair<string, object> item in dic)
            {
                if (item.Key != "ID")
                {
                    colSb.AppendFormat("TA.{0}=@P_{0},", item.Key.ToUpper());
                }
            }
            string colSql = colSb.ToString().TrimEnd(',');
            string whereSb = " WHERE TA.ID=@P_ID ";
            string sql = colSql + whereSb;
            var paraList = new List<SugarParameter>();

            foreach (KeyValuePair<string, object> item in dic)
            {
                paraList.Add(new SugarParameter(string.Format(":P_{0}", item.Key.ToUpper()), item.Value));
            }
            //paraList.Add(new SugarParameter("@P_ID", itemId, System.Data.DbType.String));
            int i = Db.Ado.ExecuteCommand(sql, paraList);
            return i > 0;
        }

        public DataTable GetById(string itemId, string formId)
        {
            var items = new HC_ITEMS_Repository().GetFieldInfoByItemId(itemId);
            if (items == null) return null;
            string tableName = string.Format("HC_{0}", items.itemVo.ITEM_NAME);
            StringBuilder sqlSb = new StringBuilder();
            sqlSb.Append("SELECT ID");
            if (items.fieldListVo != null && items.fieldListVo.Count > 0)
            {
                for (int i = 0; i < items.fieldListVo.Count; i++)
                {
                    sqlSb.AppendFormat(",{0} ", items.fieldListVo[i].ExtendFieldNAME.ToUpper());
                }
            }
            sqlSb.AppendFormat("FROM {0} WHERE ID='{1}' AND ROWNUM=1", tableName, formId);
            SugarParameter[] para = { };
            return Db.Ado.GetDataTable(sqlSb.ToString(), para);
        }

        //public List<HC_EXTEND_FIELD> GetList(string queryStr, int pageIndex, int pageSize, string sortFiled, string orderBy, ref int totalCount)
        //{
        //    var exp = Expressionable.Create<HC_EXTEND_FIELD>();
        //    exp.Or(it => it.NAME.Contains(queryStr));//拼接OR
        //    exp.Or(it => it.TITLE.Contains(queryStr));//拼接OR
        //    return Db.Queryable<HC_EXTEND_FIELD>().OrderBy(sortFiled + " " + orderBy).Where(exp.ToExpression()).ToPageList(pageIndex, pageSize, ref totalCount);
        //}

        //public bool DoEdit(HC_EXTEND_FIELD extend_field_model)
        //{
        //    return Db.Updateable<HC_EXTEND_FIELD>(extend_field_model).ExecuteCommand() > 0;
        //}

        //public bool DoDelete(string id)
        //{
        //    return Db.Deleteable<HC_EXTEND_FIELD>().Where(it => it.ID == id).ExecuteCommand() > 0;
        //}

        //public HC_EXTEND_FIELD GetById(string id)
        //{
        //    return Db.Queryable<HC_EXTEND_FIELD>().Single(it => it.ID == id);
        //}

        public bool Examine(Dictionary<string, object> dic)
        {
            if (dic == null)
                return false;
            string itemId = Utils.ObjectToStr(dic["itemId"]);
            string formIds = Utils.ObjectToStr(dic["formIds"]);
            int STATE = Utils.ObjToInt(dic["STATE"], 0);
            string AUDITSER = Utils.ObjectToStr(dic["AUDITSER"]);
            string EXAMINEREMARK = Utils.ObjectToStr(dic["ExamineRemark"]);
            var items = new HC_ITEMS_Repository().GetById(itemId);
            if (items == null) return false;
            string sql = string.Format("UPDATE HC_{0} SET STATE=:STATE,AUDITTIME=:AUDITTIME,AUDITSER=:AUDITSER WHERE ID=:ID ", items.ITEM_NAME);
            if (formIds.Length > 0)
            {
                try
                {
                    Db.Ado.BeginTran();
                    List<HC_EXAMINE> examineList = new List<HC_EXAMINE>();
                    foreach (var item in formIds.Split(','))
                    {
                        var paraList = new List<SugarParameter>
                        {
                            new SugarParameter(":STATE", STATE),
                            new SugarParameter(":AUDITTIME", DateTime.Now),
                            new SugarParameter(":AUDITSER", AUDITSER),
                            new SugarParameter(":ID", item),
                            //new SugarParameter(":ITEMID", itemId)
                        };
                        Db.Ado.ExecuteCommand(sql, paraList);
                        examineList.Add(new HC_EXAMINE()
                        {
                            ID = ResultHelper.NewId,
                            ITEMID = itemId,
                            FORMID = item,
                            STATE = STATE,
                            EXAMINEREMARK = EXAMINEREMARK,
                            EXAMINETIME = DateTime.Now
                        });
                    }
                    Db.Insertable<HC_EXAMINE>(examineList).ExecuteCommand();
                    Db.Ado.CommitTran();
                    return true;
                }
                catch (Exception)
                {
                    Db.Ado.RollbackTran();
                    return false;
                }
            }
            return false;
        }

        public bool DoDelete(string itemId, string id)
        {
            var itemForm = Db.Queryable<HC_ITEMS>().Where(m => m.ID == itemId).First();
            if (itemForm != null)
            {
                string sql = $"DELETE FROM HC_{itemForm.ITEM_NAME} WHERE ID = '{id}' ";
                return Db.Ado.ExecuteCommand(sql) > 0;
            }
            return false;
        }
    }
}
